﻿-- Remplacement de la colonne NodePropertyValueId par une colonne de type INT IDENTITY
DECLARE @alreadyApplied BIT
SELECT @alreadyApplied = ColumnProperty(OBJECT_ID('medianamik_NodePropertyValue') , 'NodePropertyValueId', 'isidentity')

IF( @alreadyApplied = 0)
BEGIN

	DISABLE TRIGGER ALL ON dbo.medianamik_nodepropertyvalue

	-- Suppression de la PK
	IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_NodePropertyValue]') AND name = N'PK_medianamik_NodeTypePropertyValue')
	ALTER TABLE [dbo].[medianamik_NodePropertyValue] DROP CONSTRAINT [PK_medianamik_NodeTypePropertyValue]

	-- Suppression de la contrainte NEWID()
	IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_medianamik_NodePropertyValue_NodePropertyValueId]') AND type = 'D')
	BEGIN
	ALTER TABLE [dbo].[medianamik_NodePropertyValue] DROP CONSTRAINT [DF_medianamik_NodePropertyValue_NodePropertyValueId]
	END

	-- Suppression de tous les index liés à la colonne
	DECLARE @dropIndexes NVARCHAR(MAX)
	SELECT @dropIndexes = ''

	select @dropIndexes = @dropIndexes + 'IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[medianamik_NodePropertyValue]'') AND name = N''' + ix.name + ''')
	BEGIN DROP INDEX [' + ix.name + '] ON [dbo].[medianamik_NodePropertyValue] WITH ( ONLINE = OFF ) END
	' from sys.indexes ix inner join
	sys.index_columns ixc on ix.index_id   = ixc.index_id  
	where ixc.object_id = OBJECT_ID('medianamik_NodePropertyValue')
	and ix.object_id = OBJECT_ID('medianamik_NodePropertyValue')

	EXEC sp_executesql @dropIndexes 

	-- Suppression de la colonne
	ALTER TABLE dbo.medianamik_nodepropertyvalue
	DROP Column NodePropertyValueId

	-- Création de la nouvelle colonne de type INT IDENTITY
	;alter table dbo.medianamik_nodepropertyvalue
	add NodePropertyValueId INT IDENTITY

	-- Création de la PK
	ALTER TABLE [dbo].[medianamik_NodePropertyValue] ADD  CONSTRAINT [PK_medianamik_NodeTypePropertyValue] PRIMARY KEY CLUSTERED 
	(
		[NodePropertyValueId] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

END

;ENABLE TRIGGER ALL ON dbo.medianamik_nodepropertyvalue

----------------------------------------------
----- Suppression des colonnes CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, IsApproved, IsDeleted sur medianamik_NodePropertyValue
DECLARE @dropContraints NVARCHAR(MAX)
SELECT @dropContraints=''

select @dropContraints = @dropContraints + 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + dc.name + ''') AND type = ''D'')
BEGIN
ALTER TABLE [dbo].[medianamik_NodePropertyValue] DROP CONSTRAINT ['+ dc.name +']
END
' from  sys.default_constraints dc
inner join sys.columns c on c.object_id = dc.parent_object_id 
and c.column_id = dc.parent_column_id 
where c.object_id = OBJECT_ID('medianamik_NodePropertyValue') 
and c.name IN ('CreatedOn','CreatedBy', 'ModifiedOn', 'ModifiedBy', 'IsApproved', 'IsDeleted')

EXEC sp_executesql @dropContraints 

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'CreatedBy'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column createdby
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'CreatedOn'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column createdOn
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'ModifiedBy'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column ModifiedBy
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'ModifiedOn'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column ModifiedOn
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'IsApproved'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column IsApproved
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'IsDeleted'
AND c.TABLE_NAME = 'medianamik_NodePropertyValue')
BEGIN
alter table medianamik_nodePropertyvalue
drop column IsDeleted
END


-- Suppression des colonnes CreatedBy, ModifiedOn, ModifiedBy de medianamik_NodeInstance
IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'CreatedBy'
AND c.TABLE_NAME = 'medianamik_NodeInstance')
BEGIN
alter table medianamik_NodeInstance
drop column CreatedBy
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'ModifiedOn'
AND c.TABLE_NAME = 'medianamik_NodeInstance')
BEGIN
alter table medianamik_NodeInstance
drop column ModifiedOn
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'ModifiedBy'
AND c.TABLE_NAME = 'medianamik_NodeInstance')
BEGIN
alter table medianamik_NodeInstance
drop column ModifiedBy
END


-- Ajout des colonnes EditedOn, EditedBy sur medianamik_Node
-- Suppression des colonnes Roles, ContentNodeId sur medianamik_Node

IF NOT EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'EditedOn'
AND c.TABLE_NAME = 'medianamik_Node')
BEGIN
alter table medianamik_Node
ADD EditedOn datetime NULL
END

IF NOT EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'EditedBy'
AND c.TABLE_NAME = 'medianamik_Node')
BEGIN
alter table medianamik_Node
ADD EditedBy NVARCHAR(50) NULL
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'Roles'
AND c.TABLE_NAME = 'medianamik_Node')
BEGIN
alter table medianamik_Node
DROP COLUMN Roles
END

IF EXISTS ( select NULL from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'ContentNodeId'
AND c.TABLE_NAME = 'medianamik_Node')
BEGIN
alter table medianamik_Node
DROP COLUMN ContentNodeId
END


-- Recréation de la vue [dbo].[vw_medianamik_HierarchicalNodeRuleFull]

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_HierarchicalNodeRuleFull]'))
BEGIN
	DECLARE @alterView NVARCHAR(MAX)
	SELECT @alterView = 'ALTER VIEW [dbo].[vw_medianamik_HierarchicalNodeRuleFull]
AS
SELECT     dbo.medianamik_HierarchicalRule.HierarchicalRuleId, dbo.medianamik_HierarchicalRule.TypeId as ChildTypeId, medianamik_Node.NodeId, medianamik_Node.ParentNodeId, medianamik_Node.TypeId, medianamik_Node.CreatedOn, medianamik_Node.CreatedBy, medianamik_Node.ModifiedOn, medianamik_Node.ModifiedBy, medianamik_Node.Flags, medianamik_Node.IsDeleted, medianamik_Node.Path, medianamik_Node.Depth, medianamik_Node.Position, medianamik_Node.EditedOn, medianamik_Node.EditedBy

FROM         dbo.medianamik_HierarchicalRule
LEFT OUTER JOIN dbo.medianamik_Node ON dbo.medianamik_HierarchicalRule.ParentItemId = dbo.medianamik_Node.NodeId
where dbo.medianamik_HierarchicalRule.ParentItemType = 1
'
	EXEC sp_executesql @alterView

END



-- CRÉATION des nouveaux index
/****** Object:  Index [_dta_index_medianamik_NodePropertyValue_15_965578478__K3_K2_K8_K7_4]    Script Date: 04/29/2009 08:57:54 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_NodePropertyValue') and name='_dta_index_medianamik_NodePropertyValue_15_965578478__K3_K2_K8_K7_4')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_NodePropertyValue_15_965578478__K3_K2_K8_K7_4] ON [dbo].[medianamik_NodePropertyValue] 
(
	[PropertyId] ASC,
	[NodeId] ASC,
	[CultureName] ASC,
	[Revision] ASC
)
INCLUDE ( [Value]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_NodePropertyValue_7_965578478__K13_K2_K1_K8_3_4_5_6_7_9_10_11_12]    Script Date: 04/29/2009 08:59:01 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_NodePropertyValue') and name='_dta_index_medianamik_NodePropertyValue_7_965578478__K13_K2_K1_K8_3_4_5_6_7_9_10_11_12')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_NodePropertyValue_7_965578478__K13_K2_K1_K8_3_4_5_6_7_9_10_11_12] ON [dbo].[medianamik_NodePropertyValue] 
(
	[EndRevision] ASC,
	[NodeId] ASC,
	[NodePropertyValueId] ASC,
	[CultureName] ASC
)
INCLUDE ( [PropertyId],
[Value],
[Revision]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


/****** Object:  Index [NodeId]    Script Date: 04/29/2009 09:00:24 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_NodePropertyValue') and name='NodeId')
CREATE NONCLUSTERED INDEX [NodeId] ON [dbo].[medianamik_NodePropertyValue] 
(
	[NodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [PropertyId]    Script Date: 04/29/2009 09:00:58 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_NodePropertyValue') and name='PropertyId')
CREATE NONCLUSTERED INDEX [PropertyId] ON [dbo].[medianamik_NodePropertyValue] 
(
	[PropertyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- AJOUT d'un index sur medianamik_NodeInstance
/****** Object:  Index [_dta_index_medianamik_NodeInstance_7_837578022__K2_1_3_4_5_6_7_8]    Script Date: 04/29/2009 09:07:32 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_NodeInstance') and name='_dta_index_medianamik_NodeInstance_7_837578022__K2_1_3_4_5_6_7_8')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_NodeInstance_7_837578022__K2_1_3_4_5_6_7_8] ON [dbo].[medianamik_NodeInstance] 
(
	[NodeId] ASC
)
INCLUDE ( [NodeInstanceId],
[IsActive],
[CreatedOn],
[CultureName]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- AJOUT d'index sur medianamik_Node
/****** Object:  Index [_dta_index_medianamik_Node_15_709577566__K10_K1_4]    Script Date: 04/29/2009 09:10:18 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Node') and name='_dta_index_medianamik_Node_15_709577566__K10_K1_4')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Node_15_709577566__K10_K1_4] ON [dbo].[medianamik_Node] 
(
	[IsDeleted] ASC,
	[NodeId] ASC
)
INCLUDE ( [TypeId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Node_15_709577566__K2_K4_K1_K13_K5_11_12]    Script Date: 04/29/2009 09:10:53 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Node') and name='_dta_index_medianamik_Node_15_709577566__K2_K4_K1_K13_K5_11_12')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Node_15_709577566__K2_K4_K1_K13_K5_11_12] ON [dbo].[medianamik_Node] 
(
	[ParentNodeId] ASC,
	[TypeId] ASC,
	[NodeId] ASC,
	[Position] ASC,
	[CreatedOn] ASC
)
INCLUDE ( [Path],
[Depth]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Node_15_709577566__K4_K11_K1_K13_K5_2_12]    Script Date: 04/29/2009 09:11:40 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Node') and name='_dta_index_medianamik_Node_15_709577566__K4_K11_K1_K13_K5_2_12')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Node_15_709577566__K4_K11_K1_K13_K5_2_12] ON [dbo].[medianamik_Node] 
(
	[TypeId] ASC,
	[Path] ASC,
	[NodeId] ASC,
	[Position] ASC,
	[CreatedOn] ASC
)
INCLUDE ( [ParentNodeId],
[Depth]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Node_7_709577566__K10_K2]    Script Date: 04/29/2009 09:12:29 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Node') and name='_dta_index_medianamik_Node_7_709577566__K10_K2')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Node_7_709577566__K10_K2] ON [dbo].[medianamik_Node] 
(
	[IsDeleted] ASC,
	[ParentNodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


-- AJOUT d'un index sur Localizations
/****** Object:  Index [_dta_index_Localizations_7_101575400__K2]    Script Date: 04/29/2009 09:13:30 ******/
if not exists (select * from sysindexes
  where id=object_id('Localizations') and name='_dta_index_Localizations_7_101575400__K2')
CREATE NONCLUSTERED INDEX [_dta_index_Localizations_7_101575400__K2] ON [dbo].[Localizations] 
(
	[ResourceId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- CREATION D'INDEX sur medianamik_Property
/****** Object:  Index [_dta_index_medianamik_Property_15_805577908__K1_3]    Script Date: 04/29/2009 09:15:58 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Property') and name='_dta_index_medianamik_Property_15_805577908__K1_3')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Property_15_805577908__K1_3] ON [dbo].[medianamik_Property] 
(
	[PropertyId] ASC
)
INCLUDE ( [Name]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Property_15_805577908__K16_K2_K1_K15_3]    Script Date: 04/29/2009 09:16:22 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Property') and name='_dta_index_medianamik_Property_15_805577908__K16_K2_K1_K15_3')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Property_15_805577908__K16_K2_K1_K15_3] ON [dbo].[medianamik_Property] 
(
	[PrimaryTypeId] ASC,
	[TypeId] ASC,
	[PropertyId] ASC,
	[PrimaryPropertyId] ASC
)
INCLUDE ( [Name]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Property_15_805577908__K2_K1_K15_K16_3]    Script Date: 04/29/2009 09:16:56 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Property') and name='_dta_index_medianamik_Property_15_805577908__K2_K1_K15_K16_3')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Property_15_805577908__K2_K1_K15_K16_3] ON [dbo].[medianamik_Property] 
(
	[TypeId] ASC,
	[PropertyId] ASC,
	[PrimaryPropertyId] ASC,
	[PrimaryTypeId] ASC
)
INCLUDE ( [Name]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Property_15_805577908__K2_K16_K1_K15_3]    Script Date: 04/29/2009 09:17:44 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Property') and name='_dta_index_medianamik_Property_15_805577908__K2_K16_K1_K15_3')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Property_15_805577908__K2_K16_K1_K15_3] ON [dbo].[medianamik_Property] 
(
	[TypeId] ASC,
	[PrimaryTypeId] ASC,
	[PropertyId] ASC,
	[PrimaryPropertyId] ASC
)
INCLUDE ( [Name]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [_dta_index_medianamik_Property_15_805577908__K3]    Script Date: 04/29/2009 09:18:11 ******/
if not exists (select * from sysindexes
  where id=object_id('medianamik_Property') and name='_dta_index_medianamik_Property_15_805577908__K3')
CREATE NONCLUSTERED INDEX [_dta_index_medianamik_Property_15_805577908__K3] ON [dbo].[medianamik_Property] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

